<?php

namespace Home\Service;

use Home\Common\FIdConst;

/**
 * 销售订单Service
 *
 * @author 李静波
 */
class RSOBillService extends PSIBaseService {
	private $LOG_CATEGORY = "销售订单";

	/**
	 * 生成新的销售订单号
	 */
	private function genNewBillRef() {
		$bs = new BizConfigService();
		$pre = $bs->getSOBillRefPre();
		
		$mid = date("Ymd");
		
		$sql = "select ref from t_so_bill where ref like '%s' order by ref desc limit 1";
		$data = M()->query($sql, $pre . $mid . "%");
		$sufLength = 3;
		$suf = str_pad("1", $sufLength, "0", STR_PAD_LEFT);
		if ($data) {
			$ref = $data[0]["ref"];
			$nextNumber = intval(substr($ref, 10)) + 1;
			$suf = str_pad($nextNumber, $sufLength, "0", STR_PAD_LEFT);
		}
		
		return $pre . $mid . $suf;
	}

    /**
     * 生成新的销售关联订单号
     */
    private function genNewRSOBillRef() {
        $bs = new BizConfigService();
        $pre = $bs->getRSOBillRefPre();

        $mid = date("Ymd");

        $sql = "select ref from t_bill_relation where ref like '%s' order by ref desc limit 1";
        $data = M()->query($sql, $pre . $mid . "%");
        $sufLength = 3;
        $suf = str_pad("1", $sufLength, "0", STR_PAD_LEFT);
        if ($data) {
            $ref = $data[0]["ref"];
            $nextNumber = intval(substr($ref, 11)) + 1;
            $suf = str_pad($nextNumber, $sufLength, "0", STR_PAD_LEFT);
        }

        return $pre . $mid . $suf;
    }

	/**
	 * 获得销售订单主表信息列表
	 */
	public function sobillList($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
        $start = $params["start"];
        $limit = $params["limit"];

        $billStatus = $params["billStatus"];
        $ref = $params["ref"];

        $db = M();

        $queryParams = array();

        $result = array();
        $sql = "select r.id,r.ref,r.type,r.bill_status,r.confirm_date,r.confirm_user_id,
                        r.sum_so_sale,r.sum_so_tax,r.sum_so_sum,r.deal_date,r.deal_user_id,u1.name as deal_user_name 
				from t_bill_relation r, t_user u1 
				where (r.deal_user_id = u1.id) and r.type = 1 ";

        if ($billStatus != - 1) {
            $sql .= " and (r.bill_status = %d) ";
            $queryParams[] = $billStatus;
        }
        if ($ref) {
            $sql .= " and (r.ref like '%s') ";
            $queryParams[] = "%$ref%";
        }

        $sql .= " order by r.deal_date desc, r.ref desc 
				  limit %d , %d";
        $queryParams[] = $start;
        $queryParams[] = $limit;
        $data = $db->query($sql, $queryParams);
        foreach ( $data as $i => $v ) {
            $result[$i]["id"] = $v["id"];
            $result[$i]["ref"] = $v["ref"];
            $result[$i]["billStatus"] = $v["bill_status"];
            $result[$i]["dealDate"] = $this->intToYMDHis($v["deal_date"]);
            $result[$i]["sumSale"] = $v["sum_so_sale"];
            $result[$i]["sumTax"] = $v["sum_so_tax"];
            $result[$i]["sumSum"] = $v["sum_so_sum"];
            $result[$i]["dealUserId"] = $v["deal_user_id"];
            $result[$i]["confirmUserId"] = $v["confirm_user_id"];
            $result[$i]["dealUserName"] = $v["deal_user_name"];

            $confirmUserId = $v["confirm_user_id"];
            if ($confirmUserId) {
                $sql = "select name from t_user where id = '%s' ";
                $d = $db->query($sql, $confirmUserId);
                if ($d) {
                    $result[$i]["confirmUserName"] = $d[0]["name"];
                    $result[$i]["confirmDate"] = $this->toYMD($v["confirm_date"]);
                }
            }
        }

        $sql = "select count(*) as cnt
				from t_bill_relation r, t_user u1
				where (r.deal_user_id = u1.id) ";
        $queryParams = array();

        if ($billStatus != - 1) {
            $sql .= " and (r.bill_status = %d) ";
            $queryParams[] = $billStatus;
        }
        if ($ref) {
            $sql .= " and (r.ref like '%s') ";
            $queryParams[] = "%$ref%";
        }

        $data = $db->query($sql, $queryParams);
        $cnt = $data[0]["cnt"];

        return array(
				"dataList" => $result,
				"totalCount" => $cnt
		);
	}

	/**
	 * 获得销售订单的信息
	 */
	public function soBillInfo($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		$id = $params["id"];
		
		$result = array();
		
		$cs = new BizConfigService();
		$result["taxRate"] = $cs->getTaxRate();
		
		$db = M();
		
		if ($id) {
			// 编辑销售订单
            $sql = "select r.id,r.ref,r.type,r.bill_status,r.confirm_date,r.confirm_user_id,
                        r.sum_so_sale,r.sum_so_tax,r.sum_so_sum,r.deal_date,r.deal_user_id,u1.name as deal_user_name 
				from t_bill_relation r, t_user u1
				where (r.id = '%s') and (r.deal_user_id = u1.id) ";

            $data = $db->query($sql, $id);

            if ($data) {
                $v = $data[0];
                $result["id"] = $v["id"];
                $result["ref"] = $v["ref"];
                $result["billStatus"] = $v["bill_status"];
                $result["dealDate"] = $this->toYMD($v["deal_date"]);
                $result["confirmDate"] = $this->toYMD($v["confirm_date"]);
                $result["sumSale"] = $v["sum_so_sale"];
                $result["sumTax"] = $v["sum_so_tax"];
                $result["sumSum"] = $v["sum_so_sum"];
                $result["dealUserId"] = $v["deal_user_id"];
                $result["confirmUserId"] = $v["confirm_user_id"];
                $result["confirmUserName"] = $v["comfirm_user_name"];
                $result["dealUserName"] = $v["deal_user_name"];

                $confirmUserId = $v["confirm_user_id"];
                if ($confirmUserId) {
                    $sql = "select name from t_user where id = '%s' ";
                    $d = $db->query($sql, $confirmUserId);
                    if ($d) {
                        $result["confirmUserName"] = $d[0]["name"];
                        $result["confirmDate"] = $v["confirm_date"];
                    }
                }

                $sql = "select s.id, bt.id as bill_type_id, bt.name as bill_type_name, s.ref, s.bill_status, s.goods_money, s.tax, s.money_with_tax,
					c.name as customer_name, s.contact, s.tel, s.fax, s.deal_address,
					s.deal_date, s.receiving_type, s.bill_memo, s.date_created,
					o.full_name as org_name, u1.name as biz_user_name, u2.name as input_user_name,
					s.confirm_user_id, s.confirm_date
				from t_so_bill s, t_customer c, t_org o, t_user u1, t_user u2, t_bill_type bt 
				where (s.customer_id = c.id) and (s.org_id = o.id)
					and (s.biz_user_id = u1.id) and (s.input_user_id = u2.id) and (s.bill_type_id = bt.id) 
					and (s.id in (select brb.bill_id from t_bill_relation_bill brb where brb.bill_relation_id = '%s')) ";

                $data = $db->query($sql,$id);

                foreach ( $data as $i => $v ) {
                    $items[$i]["id"] = $v["id"];
                    $items[$i]["ref"] = $v["ref"];
                    $items[$i]["billTypeId"] = $v["bill_type_id"];
                    $items[$i]["billType"] = $v["bill_type_name"];
                    $items[$i]["billStatus"] = $v["bill_status"];
                    $items[$i]["dealDate"] = $this->toYMD($v["deal_date"]);
                    $items[$i]["dealAddress"] = $v["deal_address"];
                    $items[$i]["customerName"] = $v["customer_name"];
                    $items[$i]["contact"] = $v["contact"];
                    $items[$i]["tel"] = $v["tel"];
                    $items[$i]["fax"] = $v["fax"];
                    $items[$i]["goodsMoney"] = $v["goods_money"];
                    $items[$i]["tax"] = $v["tax"];
                    $items[$i]["moneyWithTax"] = $v["money_with_tax"];
                    $items[$i]["receivingType"] = $v["receiving_type"];
                    $items[$i]["billMemo"] = $v["bill_memo"];
                    $items[$i]["bizUserName"] = $v["biz_user_name"];
                    $items[$i]["orgName"] = $v["org_name"];
                    $items[$i]["inputUserName"] = $v["input_user_name"];
                    $items[$i]["dateCreated"] = $v["date_created"];

                    $confirmUserId = $v["confirm_user_id"];
                    if ($confirmUserId) {
                        $sql = "select name from t_user where id = '%s' ";
                        $d = $db->query($sql, $confirmUserId);
                        if ($d) {
                            $items[$i]["confirmUserName"] = $d[0]["name"];
                            $items[$i]["confirmDate"] = $v["confirm_date"];
                        }
                    }
                }
				
				$result["items"] = $items;
			}
		} else {
			// 新建销售订单
			$us = new UserService();
			$result["bizUserId"] = $us->getLoginUserId();
			$result["bizUserName"] = $us->getLoginUserName();
			
			$sql = "select o.id, o.full_name
					from t_org o, t_user u
					where o.id = u.org_id and u.id = '%s' ";
			$data = $db->query($sql, $us->getLoginUserId());
			if ($data) {
				$result["orgId"] = $data[0]["id"];
				$result["orgFullName"] = $data[0]["full_name"];
			}
		}
		
		return $result;
	}

	/**
	 * 新增或编辑销售订单
	 */
	public function editSOBill($json) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}
		
		$bill = json_decode(html_entity_decode($json), true);
		if ($bill == null) {
			return $this->bad("传入的参数错误，不是正确的JSON格式");
		}
		
		$db = M();
		
		$db->startTrans();

        $id = $bill["id"];

        $items = $bill["items"];

        $idGen = new IdGenService();

        $log = null;
        if ($id) {
            // 编辑
            $sql = "select * from t_bill_relation where id = '%s' ";
            $data = $db->query($sql, $id);
            if (! $data) {
                $db->rollback();
                return $this->bad("要编辑的关联销售订单不存在");
            }
            $ref = $data[0]["ref"];

            $log = "编辑销售关联订单，单号：{$ref}";
        } else {
            // 新建销售关联订单

            $id = $idGen->newId();
            $ref = $this->genNewRSOBillRef();

            $us = new UserService();
            $dataOrg = $us->getLoginUserDataOrg();

            // 主表
            $sql = "insert into t_bill_relation(id, ref, bill_status,  type, sum_purchase, sum_tax,
							sum_cost, deal_date, deal_user_id)
						values ('%s', '%s', 0, 1, 0, 0, 0, %d,'%s')";
            $rc = $db->execute($sql, $id, $ref, time(), $us->getLoginUserId());
            if ($rc === false) {
                $db->rollback();
                return $this->sqlError(__LINE__);
            }

            $relation_id = $id;
            // 明细记录
            foreach ( $items as $i => $v ) {
                $bill_id = $v["bill_id"];
                // 主表
                $sql = "insert into t_bill_relation_bill( bill_relation_id, bill_id, createtime, relation_type)
                    values ( '%s', '%s', %d, 0)";
                $rc = $db->execute($sql, $relation_id, $bill_id,time());
                if ($rc === false) {
                    $db->rollback();
                    return $this->sqlError(__LINE__);
                }
            }

            $sql = "select s.id, bt.id as bill_type_id, bt.name as bill_type_name, s.ref, s.bill_status, s.goods_money, s.tax, s.money_with_tax,
					c.name as customer_name, s.contact, s.tel, s.fax, s.deal_address,
					s.deal_date, s.receiving_type, s.bill_memo, s.date_created,
					o.full_name as org_name, u1.name as biz_user_name, u2.name as input_user_name,
					s.confirm_user_id, s.confirm_date
				from t_so_bill s, t_customer c, t_org o, t_user u1, t_user u2, t_bill_type bt 
				where (s.customer_id = c.id) and (s.org_id = o.id)
					and (s.biz_user_id = u1.id) and (s.input_user_id = u2.id) and (s.bill_type_id = bt.id) 
					and (s.id in (select brb.bill_id from t_bill_relation_bill brb where brb.bill_relation_id = '%s' ))";

            $data = $db->query($sql, $relation_id);
            if (! $data) {
                $db->rollback();
                return $this->bad("要编辑的关联销售订单不存在");
            }

            $sum_so_sale = 0;
            $sum_so_tax = 0;
            $sum_so_sum = 0;

            foreach ( $data as $i => $v ) {

                $sum_so_sale += $v['goods_money'];
                $sum_so_tax += $v['tax'];
                $sum_so_sum += $v['money_with_tax'];
            }

            // 同步主表的金额合计字段
            $sql = "update t_bill_relation 
					set sum_so_sale = %f, sum_so_tax = %f, sum_so_sum = %f 
					where id = '%s' ";
            $rc = $db->execute($sql, $sum_so_sale, $sum_so_tax, $sum_so_sum,$relation_id);
            if ($rc === false) {
                $db->rollback();
                return $this->sqlError(__LINE__);
            }

            $log = "新建销售关联订单，单号：{$ref}";
        }
		
		// 记录业务日志
		if ($log) {
			$bs = new BizlogService();
			$bs->insertBizlog($log, $this->LOG_CATEGORY);
		}
		
		$db->commit();
		
		return $this->ok($id);
	}

    /**
     * 新建关联关系
     */
    public function addRSORelation($json) {
        if ($this->isNotOnline()) {
            return $this->notOnlineError();
        }

        $bill = json_decode(html_entity_decode($json), true);
        if ($bill == null) {
            return $this->bad("传入的参数错误，不是正确的JSON格式");
        }

        $db = M();

        $db->startTrans();

        $bill_id = $bill["bill_id"];
        $relation_id = $bill["relation_id"];

        $sql = "select * from t_bill_relation where id = '%s' ";
        $data = $db->query($sql, $relation_id);
        if (! $data) {
            $db->rollback();
            return $this->bad("要编辑的关联销售订单不存在");
        }
        $ref = $data[0]["ref"];

        $billStatus = $data[0]["bill_status"];
        if ($billStatus != 0) {
            $db->rollback();
            return $this->bad("当前销售关联订单已经审核，不能再编辑");
        }

//        $idGen = new IdGenService();

        $log = null;
        // 新建销售订单

//        $id = $idGen->newId();
//        $ref = $this->genNewBillRef();

        $us = new UserService();

        // 主表
        $sql = "insert into t_bill_relation_bill( bill_relation_id, bill_id, createtime, relation_type)
                    values ( '%s', '%s', %d, 0)";
        $rc = $db->execute($sql, $relation_id, $bill_id,time());
        if ($rc === false) {
            $db->rollback();
            return $this->sqlError(__LINE__);
        }

        $sql = "select s.id, bt.id as bill_type_id, bt.name as bill_type_name, s.ref, s.bill_status, s.goods_money, s.tax, s.money_with_tax,
					c.name as customer_name, s.contact, s.tel, s.fax, s.deal_address,
					s.deal_date, s.receiving_type, s.bill_memo, s.date_created,
					o.full_name as org_name, u1.name as biz_user_name, u2.name as input_user_name,
					s.confirm_user_id, s.confirm_date
				from t_so_bill s, t_customer c, t_org o, t_user u1, t_user u2, t_bill_type bt 
				where (s.customer_id = c.id) and (s.org_id = o.id)
					and (s.biz_user_id = u1.id) and (s.input_user_id = u2.id) and (s.bill_type_id = bt.id) 
					and (s.id in (select brb.bill_id from t_bill_relation_bill brb where brb.bill_relation_id = '%s' ))";

        $data = $db->query($sql, $relation_id);
        if (! $data) {
            $db->rollback();
            return $this->bad("要编辑的关联销售订单不存在");
        }

        $sql = "select s.id, bt.id as bill_type_id, bt.name as bill_type_name, s.ref, s.bill_status, s.goods_money, s.tax, s.money_with_tax,
					c.name as customer_name, s.contact, s.tel, s.fax, s.deal_address,
					s.deal_date, s.receiving_type, s.bill_memo, s.date_created,
					o.full_name as org_name, u1.name as biz_user_name, u2.name as input_user_name,
					s.confirm_user_id, s.confirm_date
				from t_so_bill s, t_customer c, t_org o, t_user u1, t_user u2, t_bill_type bt 
				where (s.customer_id = c.id) and (s.org_id = o.id)
					and (s.biz_user_id = u1.id) and (s.input_user_id = u2.id) and (s.bill_type_id = bt.id) 
					and (s.id = '%s')";
        $addData = $db->query($sql, $bill_id);
        if($addData){
            if($addData[0]['bill_status']<1){
                $db->rollback();
                return $this->bad("被关联订单必须为审核状态");
            }
        }

        $sum_so_sale = 0;
        $sum_so_tax = 0;
        $sum_so_sum = 0;

        foreach ( $data as $i => $v ) {

            $sum_so_sale += $v['goods_money'];
            $sum_so_tax += $v['tax'];
            $sum_so_sum += $v['money_with_tax'];
        }

        // 同步主表的金额合计字段
        $sql = "update t_bill_relation 
					set sum_so_sale = %f, sum_so_tax = %f, sum_so_sum = %f 
					where id = '%s' ";
        $rc = $db->execute($sql, $sum_so_sale, $sum_so_tax, $sum_so_sum,$relation_id);
        if ($rc === false) {
            $db->rollback();
            return $this->sqlError(__LINE__);
        }

        $log = "新建关联订单，单号：{$bill_id}";

        // 记录业务日志
        if ($log) {
            $bs = new BizlogService();
            $bs->insertBizlog($log, $this->LOG_CATEGORY);
        }

        $db->commit();

        return $this->ok($bill_id);
    }

    /**
     * 移除关联关系
     */
    public function removeRSORelation($json) {
        if ($this->isNotOnline()) {
            return $this->notOnlineError();
        }

        $bill = json_decode(html_entity_decode($json), true);
        if ($bill == null) {
            return $this->bad("传入的参数错误，不是正确的JSON格式");
        }

        $db = M();

        $db->startTrans();

        $bill_id = $bill["bill_id"];
        $relation_id = $bill["relation_id"];

        $sql = "select * from t_bill_relation where id = '%s' ";
        $data = $db->query($sql, $relation_id);
        if (! $data) {
            $db->rollback();
            return $this->bad("要编辑的关联销售订单不存在");
        }
        $ref = $data[0]["ref"];

        $billStatus = $data[0]["bill_status"];
        if ($billStatus != 0) {
            $db->rollback();
            return $this->bad("当前销售关联订单已经审核，不能再编辑");
        }

        $sql = "delete from t_bill_relation_bill where bill_id = '%s' ";
        $rc = $db->execute($sql, $bill_id);
        if ($rc === false) {
            $db->rollback();
            return $this->sqlError(__LINE__);
        }

        $sql = "select s.id, bt.id as bill_type_id, bt.name as bill_type_name, s.ref, s.bill_status, s.goods_money, s.tax, s.money_with_tax,
					c.name as customer_name, s.contact, s.tel, s.fax, s.deal_address,
					s.deal_date, s.receiving_type, s.bill_memo, s.date_created,
					o.full_name as org_name, u1.name as biz_user_name, u2.name as input_user_name,
					s.confirm_user_id, s.confirm_date
				from t_so_bill s, t_customer c, t_org o, t_user u1, t_user u2, t_bill_type bt 
				where (s.customer_id = c.id) and (s.org_id = o.id)
					and (s.biz_user_id = u1.id) and (s.input_user_id = u2.id) and (s.bill_type_id = bt.id) 
					and (s.id in (select brb.bill_id from t_bill_relation_bill brb where brb.bill_relation_id = '%s' ))";

        $data = $db->query($sql, $relation_id);
        if (! $data) {
            $db->rollback();
            return $this->bad("销售关联订单至少需要关联一个订单");
        }

        $sum_so_sale = 0;
        $sum_so_tax = 0;
        $sum_so_sum = 0;

        foreach ( $data as $i => $v ) {

            $sum_so_sale += $v['goods_money'];
            $sum_so_tax += $v['tax'];
            $sum_so_sum += $v['money_with_tax'];
        }

        // 同步主表的金额合计字段
        $sql = "update t_bill_relation 
					set sum_so_sale = %f, sum_so_tax = %f, sum_so_sum = %f 
					where id = '%s' ";
        $rc = $db->execute($sql, $sum_so_sale, $sum_so_tax, $sum_so_sum,$relation_id);
        if ($rc === false) {
            $db->rollback();
            return $this->sqlError(__LINE__);
        }

        $log = "删除关联订单，单号：{$bill_id}";

        // 记录业务日志
        if ($log) {
            $bs = new BizlogService();
            $bs->insertBizlog($log, $this->LOG_CATEGORY);
        }

        $db->commit();

        return $this->ok($bill_id);
    }

	/**
	 * 获得销售订单的明细信息
	 */
	public function soBillDetailList($params) {
		if ($this->isNotOnline()) {
			return $this->emptyResult();
		}
		
		$id = $params["id"];
		$db = M();

        $sql = "select s.id, bt.id as bill_type_id, bt.name as bill_type_name, s.ref, s.bill_status, s.goods_money, s.tax, s.money_with_tax,
					c.name as customer_name, s.contact, s.tel, s.fax, s.deal_address,
					s.deal_date, s.receiving_type, s.bill_memo, s.date_created,
					o.full_name as org_name, u1.name as biz_user_name, u2.name as input_user_name,
					s.confirm_user_id, s.confirm_date
				from t_so_bill s, t_customer c, t_org o, t_user u1, t_user u2, t_bill_type bt 
				where (s.customer_id = c.id) and (s.org_id = o.id)
					and (s.biz_user_id = u1.id) and (s.input_user_id = u2.id) and (s.bill_type_id = bt.id) 
					and (s.id in (select rb.bill_id from t_bill_relation_bill rb where rb.bill_relation_id = '%s' and rb.relation_type = 0))";

        $data = $db->query($sql, $id);
        foreach ( $data as $i => $v ) {
            $result[$i]["id"] = $v["id"];
            $result[$i]["ref"] = $v["ref"];
            $result[$i]["billTypeId"] = $v["bill_type_id"];
            $result[$i]["billType"] = $v["bill_type_name"];
            $result[$i]["billStatus"] = $v["bill_status"];
            $result[$i]["dealDate"] = $this->toYMD($v["deal_date"]);
            $result[$i]["dealAddress"] = $v["deal_address"];
            $result[$i]["customerName"] = $v["customer_name"];
            $result[$i]["contact"] = $v["contact"];
            $result[$i]["tel"] = $v["tel"];
            $result[$i]["fax"] = $v["fax"];
            $result[$i]["goodsMoney"] = $v["goods_money"];
            $result[$i]["tax"] = $v["tax"];
            $result[$i]["moneyWithTax"] = $v["money_with_tax"];
            $result[$i]["receivingType"] = $v["receiving_type"];
            $result[$i]["billMemo"] = $v["bill_memo"];
            $result[$i]["bizUserName"] = $v["biz_user_name"];
            $result[$i]["orgName"] = $v["org_name"];
            $result[$i]["inputUserName"] = $v["input_user_name"];
            $result[$i]["dateCreated"] = $v["date_created"];

            $confirmUserId = $v["confirm_user_id"];
            if ($confirmUserId) {
                $sql = "select name from t_user where id = '%s' ";
                $d = $db->query($sql, $confirmUserId);
                if ($d) {
                    $result[$i]["confirmUserName"] = $d[0]["name"];
                    $result[$i]["confirmDate"] = $v["confirm_date"];
                }
            }
        }

		return $result;
	}

	/**
	 * 删除销售订单
	 */
	public function deleteSOBill($params) {
		if ($this->isNotOnline()) {
			return $this->notOnlineError();
		}

        $id = $params["id"];
        $db = M();

        $db->startTrans();

        $sql = "select ref, bill_status from t_bill_relation where id = '%s' ";
        $data = $db->query($sql, $id);
        if (! $data) {
            $db->rollback();
            return $this->bad("要删除的销售订单不存在");
        }
        $ref = $data[0]["ref"];
        $billStatus = $data[0]["bill_status"];
        if ($billStatus > 0) {
            $db->rollback();
            return $this->bad("销售订单(单号：{$ref})已经审核，不能被删除");
        }

        $sql = "delete from t_bill_relation_bill where bill_relation_id = '%s' ";
        $rc = $db->execute($sql, $id);
        if ($rc === false) {
            $db->rollback();
            return $this->sqlError(__LINE__);
        }

        $sql = "delete from t_bill_relation where id = '%s' ";
        $rc = $db->execute($sql, $id);
        if ($rc === false) {
            $db->rollback();
            return $this->sqlError(__LINE__);
        }

        $log = "删除销售关联订单，单号：{$ref}";
        $bs = new BizlogService();
        $bs->insertBizlog($log, $this->LOG_CATEGORY);

        $db->commit();
		
		return $this->ok();
	}



    /**
     * 获得销售订单主表信息列表
     */
    public function rsoEditBillList($params) {
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        $start = $params["start"];
        $limit = $params["limit"];

        $billStatus = $params["billStatus"];
        $ref = $params["ref"];
        $fromDT = $params["fromDT"];
        $toDT = $params["toDT"];
        $customerId = $params["customerId"];
        $receivingType = $params["receivingType"];

        $selectIds = $params['selectIds'];

        $db = M();

        $queryParams = array();

        $result = array();
        $sql = "select s.id, bt.id as bill_type_id, bt.name as bill_type_name, s.ref, s.bill_status, s.goods_money, s.tax, s.money_with_tax,
					c.name as customer_name, s.contact, s.tel, s.fax, s.deal_address,
					s.deal_date, s.receiving_type, s.bill_memo, s.date_created,
					o.full_name as org_name, u1.name as biz_user_name, u2.name as input_user_name,
					s.confirm_user_id, s.confirm_date
				from t_so_bill s, t_customer c, t_org o, t_user u1, t_user u2, t_bill_type bt 
				where (s.customer_id = c.id) and (s.org_id = o.id)
					and (s.biz_user_id = u1.id) and (s.input_user_id = u2.id) and (s.bill_type_id = bt.id) 
					and (s.id not in (select brb.bill_id from t_bill_relation_bill brb)) ";

        if($selectIds){
            $sql .= " and (s.id not in (";
            $sql .= $selectIds;
            $sql .= ")) ";
        }

        $ds = new DataOrgService();
        $rs = $ds->buildSQL(FIdConst::SALE_ORDER, "s");
        if ($rs) {
            $sql .= " and " . $rs[0];
            $queryParams = $rs[1];
        }

        if ($billStatus != - 1) {
            $sql .= " and (s.bill_status = %d) ";
            $queryParams[] = $billStatus;
        }
        if ($ref) {
            $sql .= " and (s.ref like '%s') ";
            $queryParams[] = "%$ref%";
        }
        if ($fromDT) {
            $sql .= " and (s.deal_date >= '%s')";
            $queryParams[] = $fromDT;
        }
        if ($toDT) {
            $sql .= " and (s.deal_date <= '%s')";
            $queryParams[] = $toDT;
        }
        if ($customerId) {
            $sql .= " and (s.customer_id = '%s')";
            $queryParams[] = $customerId;
        }
        if ($receivingType != - 1) {
            $sql .= " and (s.receiving_type = %d) ";
            $queryParams[] = $receivingType;
        }
        $sql .= " order by s.deal_date desc, s.ref desc 
				  limit %d , %d";
        $queryParams[] = $start;
        $queryParams[] = $limit;
        $data = $db->query($sql, $queryParams);
        foreach ( $data as $i => $v ) {
            $result[$i]["id"] = $v["id"];
            $result[$i]["ref"] = $v["ref"];
            $result[$i]["billTypeId"] = $v["bill_type_id"];
            $result[$i]["billType"] = $v["bill_type_name"];
            $result[$i]["billStatus"] = $v["bill_status"];
            $result[$i]["dealDate"] = $this->toYMD($v["deal_date"]);
            $result[$i]["dealAddress"] = $v["deal_address"];
            $result[$i]["customerName"] = $v["customer_name"];
            $result[$i]["contact"] = $v["contact"];
            $result[$i]["tel"] = $v["tel"];
            $result[$i]["fax"] = $v["fax"];
            $result[$i]["goodsMoney"] = $v["goods_money"];
            $result[$i]["tax"] = $v["tax"];
            $result[$i]["moneyWithTax"] = $v["money_with_tax"];
            $result[$i]["receivingType"] = $v["receiving_type"];
            $result[$i]["billMemo"] = $v["bill_memo"];
            $result[$i]["bizUserName"] = $v["biz_user_name"];
            $result[$i]["orgName"] = $v["org_name"];
            $result[$i]["inputUserName"] = $v["input_user_name"];
            $result[$i]["dateCreated"] = $v["date_created"];

            $confirmUserId = $v["confirm_user_id"];
            if ($confirmUserId) {
                $sql = "select name from t_user where id = '%s' ";
                $d = $db->query($sql, $confirmUserId);
                if ($d) {
                    $result[$i]["confirmUserName"] = $d[0]["name"];
                    $result[$i]["confirmDate"] = $v["confirm_date"];
                }
            }
        }

        $sql = "select count(*) as cnt
				from t_so_bill s, t_customer c, t_org o, t_user u1, t_user u2
				where (s.customer_id = c.id) and (s.org_id = o.id)
					and (s.biz_user_id = u1.id) and (s.input_user_id = u2.id)
				";
        $queryParams = array();
        $ds = new DataOrgService();
        $rs = $ds->buildSQL(FIdConst::SALE_ORDER, "s");
        if ($rs) {
            $sql .= " and " . $rs[0];
            $queryParams = $rs[1];
        }
        if ($billStatus != - 1) {
            $sql .= " and (s.bill_status = %d) ";
            $queryParams[] = $billStatus;
        }
        if ($ref) {
            $sql .= " and (s.ref like '%s') ";
            $queryParams[] = "%$ref%";
        }
        if ($fromDT) {
            $sql .= " and (s.deal_date >= '%s')";
            $queryParams[] = $fromDT;
        }
        if ($toDT) {
            $sql .= " and (s.deal_date <= '%s')";
            $queryParams[] = $toDT;
        }
        if ($customerId) {
            $sql .= " and (s.customer_id = '%s')";
            $queryParams[] = $customerId;
        }
        if ($receivingType != - 1) {
            $sql .= " and (s.receiving_type = %d) ";
            $queryParams[] = $receivingType;
        }
        $data = $db->query($sql, $queryParams);
        $cnt = $data[0]["cnt"];

        return array(
            "dataList" => $result,
            "totalCount" => $cnt
        );
    }

    /**
     * 销售订单的商品明细
     */
    public function soBillDetail2List($params) {
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        $id = $params["id"];
        $selectIds = $params["selectIds"];
        $db = M();
        $result = array();
        if(!empty($selectIds)){
            $sql = "select s.id, g.code, g.name, g.spec, s.goods_count, s.goods_price, s.goods_price_tax, s.goods_money,
					s.tax_rate, s.tax, s.money_with_tax, u.name as unit_name
				from t_so_bill_detail s, t_goods g, t_goods_unit u
				where s.sobill_id in (".$selectIds.") 
                and s.goods_id = g.id and g.unit_id = u.id
				order by s.show_order";
            $data = $db->query($sql);
        }else {
            $sql = "select s.id, g.code, g.name, g.spec, s.goods_count, s.goods_price, s.goods_price_tax, s.goods_money,
					s.tax_rate, s.tax, s.money_with_tax, u.name as unit_name
				from t_so_bill_detail s, t_goods g, t_goods_unit u
				where s.sobill_id in (select rb.bill_id from t_bill_relation_bill rb where rb.bill_relation_id = '%s' and rb.relation_type = 0) 
                and s.goods_id = g.id and g.unit_id = u.id
				order by s.show_order";

            $data = $db->query($sql, $id);
        }

        foreach ( $data as $i => $v ) {
            $result[$i]["id"] = $v["id"];
            $result[$i]["goodsCode"] = $v["code"];
            $result[$i]["goodsName"] = $v["name"];
            $result[$i]["goodsSpec"] = $v["spec"];
            $result[$i]["goodsCount"] = $v["goods_count"];
            $result[$i]["goodsPrice"] = $v["goods_price"];
            $result[$i]["goodsPriceTax"] = $v["goods_price_tax"];
            $result[$i]["goodsMoney"] = $v["goods_money"];
            $result[$i]["taxRate"] = $v["tax_rate"];
            $result[$i]["tax"] = $v["tax"];
            $result[$i]["moneyWithTax"] = $v["money_with_tax"];
            $result[$i]["unitName"] = $v["unit_name"];
        }

        return $result;
    }

    /**
     * 采购订单的销售订单明细
     */
    public function soBillDetail3List($params) {
        if ($this->isNotOnline()) {
            return $this->emptyResult();
        }

        $id = $params["id"];
        $db = M();
        $result = array();
        $sql = "select p.id, p.ref,p.bill_type_id,bt.name as bill_type_name, p.bill_status, p.goods_money, p.tax, p.money_with_tax,
					s.name as supplier_name, p.contact, p.tel, p.fax, p.deal_address,
					p.deal_date, p.payment_type, p.bill_memo, p.date_created,
					o.full_name as org_name, u1.name as biz_user_name, u2.name as input_user_name,
					p.confirm_user_id, p.confirm_date
				from t_po_bill p, t_supplier s, t_org o, t_user u1, t_user u2, t_bill_type bt 
				where (p.supplier_id = s.id) and (p.org_id = o.id)
					and (p.biz_user_id = u1.id) and (p.input_user_id = u2.id) and (p.bill_type_id = bt.id)
					and (p.id in (select brb.bill_id from t_bill_relation_bill brb where brb.bill_relation_id = '%s' and brb.relation_type = 1)) ";

        $data = $db->query($sql, $id);
        foreach ( $data as $i => $v ) {
            $result[$i]["id"] = $v["id"];
            $result[$i]["ref"] = $v["ref"];
            $result[$i]["bill_type_id"] = $v["bill_type_id"];
            $result[$i]["billType"] = $v["bill_type_name"];
            $result[$i]["billStatus"] = $v["bill_status"];
            $result[$i]["dealDate"] = $this->toYMD($v["deal_date"]);
            $result[$i]["dealAddress"] = $v["deal_address"];
            $result[$i]["supplierName"] = $v["supplier_name"];
            $result[$i]["contact"] = $v["contact"];
            $result[$i]["tel"] = $v["tel"];
            $result[$i]["fax"] = $v["fax"];
            $result[$i]["goodsMoney"] = $v["goods_money"];
            $result[$i]["tax"] = $v["tax"];
            $result[$i]["moneyWithTax"] = $v["money_with_tax"];
            $result[$i]["paymentType"] = $v["payment_type"];
            $result[$i]["billMemo"] = $v["bill_memo"];
            $result[$i]["bizUserName"] = $v["biz_user_name"];
            $result[$i]["orgName"] = $v["org_name"];
            $result[$i]["inputUserName"] = $v["input_user_name"];
            $result[$i]["dateCreated"] = $v["date_created"];

            $confirmUserId = $v["confirm_user_id"];
            if ($confirmUserId) {
                $sql = "select name from t_user where id = '%s' ";
                $d = $db->query($sql, $confirmUserId);
                if ($d) {
                    $result[$i]["confirmUserName"] = $d[0]["name"];
                    $result[$i]["confirmDate"] = $v["confirm_date"];
                }
            }
        }

        return $result;
    }

    /**
     * 新建关联关系
     */
    public function addRSOBillPurchase($json) {
        if ($this->isNotOnline()) {
            return $this->notOnlineError();
        }

        $bill = json_decode(html_entity_decode($json), true);
        if ($bill == null) {
            return $this->bad("传入的参数错误，不是正确的JSON格式");
        }

        $db = M();

        $db->startTrans();

        $bill_id = $bill["bill_id"];
        $relation_id = $bill["relation_id"];

        $sql = "select * from t_bill_relation where id = '%s' ";
        $data = $db->query($sql, $relation_id);
        if (! $data) {
            $db->rollback();
            return $this->bad("要编辑的销售关联订单不存在");
        }
        $ref = $data[0]["ref"];

        $billStatus = $data[0]["bill_status"];
        if ($billStatus != 0) {
            $db->rollback();
            return $this->bad("当前销售关联订单已经审核，不能再编辑");
        }

        $sql = "select * from t_bill_relation_bill where bill_relation_id = '%s' and relation_type = 1";
        $data = $db->query($sql, $relation_id);
        if ($data) {
            $db->rollback();
            return $this->bad("该采购关联订单已关联了一个销售订单，不可关联多个采购订单".count($data));
        }

        $log = null;

        $sql = "insert into t_bill_relation_bill( bill_relation_id, bill_id, createtime, relation_type)
                    values ( '%s', '%s', %d, 1)";
        $rc = $db->execute($sql, $relation_id, $bill_id,time());
        if ($rc === false) {
            $db->rollback();
            return $this->sqlError(__LINE__);
        }

        $sql = "select p.id, p.ref,p.bill_type_id,bt.name as bill_type_name, p.bill_status, p.goods_money, p.tax, p.money_with_tax,
					s.name as supplier_name, p.contact, p.tel, p.fax, p.deal_address,
					p.deal_date, p.payment_type, p.bill_memo, p.date_created,
					o.full_name as org_name, u1.name as biz_user_name, u2.name as input_user_name,
					p.confirm_user_id, p.confirm_date
				from t_po_bill p, t_supplier s, t_org o, t_user u1, t_user u2, t_bill_type bt 
				where (p.supplier_id = s.id) and (p.org_id = o.id)
					and (p.biz_user_id = u1.id) and (p.input_user_id = u2.id) and (p.bill_type_id = bt.id) 
					and (p.id = '%s')";
        $addData = $db->query($sql, $bill_id);
        if($addData){
            if($addData[0]['bill_status']<1){
                $db->rollback();
                return $this->bad("被关联订单必须为审核状态");
            }
        }

        $sql = "select p.id, g.code, g.name, g.spec, p.goods_count, p.goods_price, p.goods_price_tax, p.goods_money, 
					p.tax_rate, p.tax, p.money_with_tax, u.name as unit_name
				from t_po_bill_detail p, t_goods g, t_goods_unit u
				where p.pobill_id = '%s'
				and p.goods_id = g.id and g.unit_id = u.id
				order by p.show_order";

        $goods1 = $db->query($sql, $bill_id);

        $sql = "select s.id, s.goods_id, g.code, g.name, g.spec, s.goods_count, s.goods_price, s.goods_price_tax, s.goods_money, s.tax_rate, s.tax, s.money_with_tax, u.name as unit_name
				from t_so_bill_detail s, t_goods g, t_goods_unit u
				where s.sobill_id in (select rb.bill_id from t_bill_relation_bill rb where rb.bill_relation_id = '%s' and rb.relation_type = 0) 
				and s.goods_id = g.id and g.unit_id = u.id
				order by s.show_order";

        $goods2 = $db->query($sql, $relation_id);

        $data = [];

        foreach ( $goods2 as $i => $v ) {
            $data[$v['code']]['sum'] += $v['goods_count'];
        }

        foreach ( $goods1 as $i => $v ) {
            if($v['goods_count']<$data[$v['code']]['sum']){
                $db->rollback();
                return $this->bad("商品".$v['name']."采购数量不足，无法关联");
            }
        }

        $log = "销售关联订单 关联采购订单，单号：{$bill_id}";

        // 记录业务日志
        if ($log) {
            $bs = new BizlogService();
            $bs->insertBizlog($log, $this->LOG_CATEGORY);
        }

        $db->commit();

        return $this->ok($bill_id);
    }

    /**
     * 移除关联关系
     */
    public function removeRSOBillPurchase($id) {
        if ($this->isNotOnline()) {
            return $this->notOnlineError();
        }

//        $bill = json_decode(html_entity_decode($json), true);
//        if ($bill == null) {
//            return $this->bad("传入的参数错误，不是正确的JSON格式");
//        }

        $db = M();

        $db->startTrans();

//        $bill_id = $bill["bill_id"];
        $relation_id = $id;

        $sql = "select * from t_bill_relation where id = '%s' ";
        $data = $db->query($sql, $relation_id);
        if (! $data) {
            $db->rollback();
            return $this->bad("要编辑的采购关联订单不存在");
        }
        $ref = $data[0]["ref"];

        $billStatus = $data[0]["bill_status"];
        if ($billStatus != 0) {
            $db->rollback();
            return $this->bad("当前采购关联订单已经审核，不能再编辑");
        }

        $sql = "select * from t_bill_relation_bill where bill_relation_id = '%s' and relation_type = 1";
        $data = $db->query($sql, $relation_id);
        if (!$data) {
            $db->rollback();
            return $this->bad("该采购关联订单未关联销售订单");
        }

        $log = null;

        $sql = "delete from t_bill_relation_bill where bill_relation_id = '%s' and relation_type = 1";
        $rc = $db->execute($sql, $relation_id);
        if ($rc === false) {
            $db->rollback();
            return $this->sqlError(__LINE__);
        }

        $log = "采购关联订单 移除销售订单，单号：{$relation_id}";

        // 记录业务日志
        if ($log) {
            $bs = new BizlogService();
            $bs->insertBizlog($log, $this->LOG_CATEGORY);
        }

        $db->commit();

        return $this->ok($relation_id);
    }

    /**
     * 审核采购订单
     */
    public function commitSOBill($params) {
        if ($this->isNotOnline()) {
            return $this->notOnlineError();
        }

        $id = $params["id"];
        $db = M();

        $db->startTrans();

        $sql = "select ref, bill_status from t_bill_relation where id = '%s' ";
        $data = $db->query($sql, $id);
        if (! $data) {
            $db->rollback();
            return $this->bad("要审核的采购订单不存在");
        }
        $ref = $data[0]["ref"];
        $billStatus = $data[0]["bill_status"];
        if ($billStatus > 0) {
            $db->rollback();
            return $this->bad("采购订单(单号：$ref)已经被审核，不能再次审核");
        }

        $sql = "update t_bill_relation
					set bill_status = 1000,
						confirm_user_id = '%s',
						confirm_date = now()
					where id = '%s' ";
        $us = new UserService();
        $rc = $db->execute($sql, $us->getLoginUserId(), $id);
        if ($rc === false) {
            $db->rollback();
            return $this->sqlError(__LINE__);
        }

        //子订单审核状态变更
        $sql = "select pb.id,pb.ref, pb.bill_status from t_so_bill pb where pb.id in (select brb.bill_id from t_bill_relation_bill brb where brb.bill_relation_id = '%s') ";
        $data = $db->query($sql, $id);
        if (! $data) {
            $db->rollback();
            return $this->bad("要审核的子订单不存在");
        }

        foreach ($data as $i => $v ){
            $ref = $v["ref"];
            $billStatus = $v["bill_status"];
            if ($billStatus > 0) {

            }else{
                $sql = "update t_po_bill
					set bill_status = 1000,
						confirm_user_id = '%s',
						confirm_date = now()
					where id = '%s' ";
                $us = new UserService();
                $rc = $db->execute($sql, $us->getLoginUserId(), $v['id']);
                if ($rc === false) {
                    $db->rollback();
                    return $this->sqlError(__LINE__);
                }
            }
        }

        // 记录业务日志
        $log = "审核销售关联订单，单号：{$ref}";
        $bs = new BizlogService();
        $bs->insertBizlog($log, $this->LOG_CATEGORY);

        $db->commit();

        return $this->ok($id);
    }


    /**
     * 取消审核销售订单
     */
    public function cancelConfirmSOBill($params) {
        if ($this->isNotOnline()) {
            return $this->notOnlineError();
        }

        $id = $params["id"];
        $db = M();

        $db->startTrans();

        $sql = "select ref, bill_status from t_bill_relation where id = '%s' ";
        $data = $db->query($sql, $id);
        if (! $data) {
            $db->rollback();
            return $this->bad("要取消审核的销售订单不存在");
        }
        $ref = $data[0]["ref"];
        $billStatus = $data[0]["bill_status"];
        if ($billStatus > 1000) {
            $db->rollback();
            return $this->bad("销售订单(单号:{$ref})不能取消审核");
        }

//		$sql = "select count(*) as cnt from t_po_pw where po_id = '%s' ";
//		$data = $db->query($sql, $id);
//		$cnt = $data[0]["cnt"];
//		if ($cnt > 0) {
//			$db->rollback();
//			return $this->bad("销售订单(单号:{$ref})已经生成了采购入库单，不能取消审核");
//		}

        $sql = "update t_bill_relation
					set bill_status = 0, confirm_user_id = null, confirm_date = null
					where id = '%s' ";
        $rc = $db->execute($sql, $id);
        if ($rc === false) {
            $db->rollback();
            return $this->sqlError(__LINE__);
        }

        // 记录业务日志
        $log = "取消审核销售订单，单号：{$ref}";
        $bs = new BizlogService();
        $bs->insertBizlog($log, $this->LOG_CATEGORY);

        $db->commit();

        return $this->ok($id);
    }
}